package com.mmp.main.DB;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mmp.common.DB.DBManager;
import com.mmp.main.entity.HospitalDevice;
import com.mmp.main.entity.JobOrder;
import com.mmp.main.util.Default;
import com.mmp.main.util.Enums;

public class DBQueryManager {

	protected DBManager dbm;

	public DBQueryManager(DBManager dbm) {
		this.dbm = dbm;
	}

	public HospitalDevice getDevice(String code, String sn, Enums.DEVICE_STATUS status, boolean searchForAllButScrapped) {

		if ((code == null || code.isEmpty()) && (sn == null || sn.isEmpty()))
			return null;

		String jpql = "select h from HospitalDevice h where ";
		int i = 0;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (status != null)
			jpql += "h.status = " + status.getStatus();
		else if (searchForAllButScrapped)
			jpql += "h.status != " + Enums.DEVICE_STATUS.SCRAPPED.getStatus();
		else
			jpql += "h.status in (0,1,2)";

		if (code != null && !code.trim().isEmpty()) {
			jpql += " and h.code = ? ";
			map.put(++i, code);
		}

		if (sn != null && !sn.trim().isEmpty()) {
			jpql += " and h.serialNo = ? ";
			map.put(++i, sn);
		}

		List<HospitalDevice> list = dbm.queryJPQL(jpql, map, HospitalDevice.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public JobOrder getOpenedJobOrder(String jobOrderNo, String code, String sn, Enums.DEVICE_STATUS status) {

		if ((jobOrderNo == null || jobOrderNo.isEmpty()) && (code == null || code.isEmpty())
				&& (sn == null || sn.isEmpty()))
			return null;

		String jpql = "select j from JobOrder j where j.closed = false and j.cancelled = false ";

		int i = 0;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (jobOrderNo != null && !jobOrderNo.trim().isEmpty()) {
			jpql += " and j.jobOrderNo = ? ";
			map.put(++i, jobOrderNo);
		}

		if (code != null && !code.trim().isEmpty()) {
			jpql += " and j.hospitalDevice.code = ? ";
			map.put(++i, code);
		}

		if (sn != null && !sn.trim().isEmpty()) {
			jpql += " and j.hospitalDevice.serialNo = ? ";
			map.put(++i, sn);
		}

		if (status != null) {
			jpql += " and j.hospitalDevice.status = ? ";
			map.put(++i, status.getStatus());
		}

		List<JobOrder> list = dbm.queryJPQL(jpql, map, JobOrder.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public HospitalDevice checkDeviceCodeOrSerialDuplication(String code, String sn, int excludedDeviceId) {

		if ((code == null || code.isEmpty()) && (sn == null || sn.isEmpty()))
			return null;

		String jpql = "select h from HospitalDevice h where (h.code = ? or h.serialNo = ?) and h.id != ?";

		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(1, code);
		map.put(2, sn);
		map.put(3, excludedDeviceId);

		List<HospitalDevice> list = dbm.queryJPQL(jpql, map, HospitalDevice.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public List<JobOrder> getLateJobOrdersForNotifications() {
		String jpql = "SELECT j FROM JobOrder j where DATEDIFF(now(),j.jobOrderDate) >= 4 and j.secondAction is null and j.secondActionDate is null and j.closed = false and j.cancelled = false";
		List<JobOrder> jOrderList = dbm.queryJPQL(jpql, null, JobOrder.class);
		return jOrderList;
	}

	public String getNotification() {
		List<JobOrder> list = getLateJobOrdersForNotifications();
		if (list == null || list.isEmpty())
			return "";

		String qStrFirstPart = Default.SEARCH_DEST_PARAM_NAME + "="
				+ Default.SEARCH_DEST_FOLLOWUP_JOB_OREDR_PARAM_VALUE;

		String notfHtml = "<div><img src='image/warning.png' />" + "يوجد أوامر عمل تحتاج متابعة وأرقامها هي : "
				+ "<a href='SearchForJobOrderProcess?" + qStrFirstPart + "&jobOrderNo=" + list.get(0).getJobOrderNo()
				+ "'>" + list.get(0).getJobOrderNo() + "</a>";
		for (int i = 1; i < list.size(); i++)
			notfHtml += " , " + "<a href='SearchForJobOrderProcess?" + qStrFirstPart + "&jobOrderNo="
					+ list.get(i).getJobOrderNo() + "'>" + list.get(i).getJobOrderNo() + "</a>";

		return notfHtml + "</div>";

	}
}
